package cn.jilian.common.service.impl;

import java.io.File;
import java.io.InputStream;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.io.FileUtils;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Service;

import cn.jilian.common.domain.FileInfo;
import cn.jilian.common.service.FileService;

/**
 * 基于数据库BOLB字段的文件存储服务
 * */
@Service
public class DBStoreFileService implements FileService {
	
	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	private  LobHandler lobHandler=new DefaultLobHandler();
	@Override
	public long add(final long fid, final String name,final InputStream is, final String ext, final long size,final String originalName,final long meta) {
		FileInfo info=get(fid,name,meta);
		if(info!=null)return -1;
		final Long sid=jdbcTemplate.queryForObject("select nextval('seq_sid')", Long.class);
		final int seq=0;
		final int rank=1;
		String sql="insert into bt_file(sid,fid,seq,name,code,ext,text,size,flag,rank,meta,tims)values(?,?,?,?,?,?,?,?,1,?,?,now())";
		int x=jdbcTemplate.execute(sql,new AbstractLobCreatingPreparedStatementCallback(lobHandler){

							@Override
							protected void setValues(PreparedStatement ps,
									LobCreator lobCreator) throws SQLException,
									DataAccessException {
								ps.setLong(1, sid);
								ps.setLong(2, fid);
								ps.setInt(3, seq);
								ps.setString(4,name);
								lobCreator.setBlobAsBinaryStream(ps, 5, is,(int)size);
								ps.setString(6,ext);
								ps.setString(7, originalName);
								ps.setLong(8, size);
								ps.setLong(9, rank);
								ps.setLong(10, meta);
							}
					
				});
		if(x>0)
		return sid;
		return -2;
	}

	@Override
	public FileInfo get(long fid, String name, long meta) {
		String sql="select * from bt_file where fid=? and name=? and meta=?";
		List<FileInfo> list=jdbcTemplate.query(sql,new Object[] {fid,name,meta}, new RowMapper<FileInfo>() {

			@Override
			public FileInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
				FileInfo info=new FileInfo();
				info.setSid(rs.getLong("sid"));
				info.setFid(rs.getLong("fid"));
				info.setCode(rs.getBlob("code").getBinaryStream());
				info.setName(rs.getString("name"));
				info.setExt(rs.getString("ext"));
				info.setText(rs.getString("text"));
				info.setRank(rs.getLong("rank"));
				info.setMeta(rs.getLong("meta"));
				info.setSeq(rs.getInt("seq"));
				info.setSize(rs.getLong("size"));
				return info;
			}
			
		});
		if(CollectionUtils.isEmpty(list))
		return null;
		return list.get(0);
	}

	@Override
	public int update( final FileInfo info) {
		Long sid=info.getSid();
		if(sid!=null) {
			String sql="update bt_file set code=?,size=?,ext=?,text=?,tims=now() where sid=?";
			return jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler){

				@Override
				protected void setValues(PreparedStatement ps,
						LobCreator lobCreator) throws SQLException,
						DataAccessException {
					int size=((Number)info.getSize()).intValue();
					lobCreator.setBlobAsBinaryStream(ps, 1, info.getCode(),size);
					ps.setInt(2, size);
					ps.setString(3,info.getExt());
					ps.setString(4, info.getText());
					ps.setLong(5,info.getSid());
				}
				
			});

		}else {
			String sql="update bt_file set code=?,size=?,ext=?,text=?,tims=now() where fid=? and name=? and meta=?";
			return jdbcTemplate.execute(sql, new AbstractLobCreatingPreparedStatementCallback(lobHandler){

				@Override
				protected void setValues(PreparedStatement ps,
						LobCreator lobCreator) throws SQLException,
						DataAccessException {
					int s=((Number)info.getSize()).intValue();
					lobCreator.setBlobAsBinaryStream(ps, 1, info.getCode(),s);
					ps.setInt(2, s);
					ps.setString(3,info.getExt());
					ps.setString(4, info.getText());
					ps.setLong(5,info.getFid());
					ps.setString(6, info.getName());
					ps.setLong(7, info.getMeta());
				}
				
			});
		}
	}

	@Override
	public int delete(long sid) {
		String sql="delete from bt_file where sid=?";
		
		return jdbcTemplate.update(sql,sid);
	}

	@Override
	public int delete(long fid, String name, long meta) {
		String sql="delete from bt_file where fid=? and name=? and meta=?";
		return jdbcTemplate.update(sql,fid,name,meta);
	}

	@Override
	public int delete(long fid, long meta) {
		String sql="delete from bt_file where fid=? and meta=?";
		return jdbcTemplate.update(sql,fid,meta);
	}

	@Override
	public Map<String,FileInfo> list(long fid, long meta) {
		String sql="select * from bt_file where fid=? and meta=?";
		final Map<String,FileInfo> result=new HashMap<>();
		jdbcTemplate.query(sql, new Object[]{fid,meta}, new RowCallbackHandler(){

			@Override
			public void processRow(ResultSet rs) throws SQLException {
				FileInfo fs=new FileInfo();
				fs.setCode(rs.getBlob("CODE").getBinaryStream());
				fs.setName(rs.getString("NAME"));
				fs.setExt(rs.getString("TEXT"));
				fs.setSid(rs.getLong("SID"));
				result.put(fs.getName(), fs);
			}
		});
		return result;
	}

	@Override
	public long save(long fid, String name, InputStream is, String ext, long size, String originalName, long meta) {
		FileInfo rec= get(fid,name,meta);
		if(rec!=null) {
			rec.setCode(is);
			rec.setExt(ext);
			rec.setSize(size);
			rec.setText(originalName);
			update(rec);
			return rec.getSid();
		}else {
			return add(fid,name,is,ext,size,originalName,meta);
		}
	}

	@Override
	public long save(long fid, String name, File file, long meta) {
		try {
		InputStream is=FileUtils.openInputStream(file);
		String ext=StringUtils.substringAfter(file.getName(), ".");
		long size=FileUtils.sizeOf(file);
		String originalName=file.getName();
		return save(fid, name, is, ext, size, originalName, meta);
		}catch(Exception e) {
			e.printStackTrace();
			return 0;
		}
	}

}
